package org.example;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class DatabaseOperations {

    // 添加用户
    public static boolean addUser(String username, String password, String address, String phone) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = Util.getConn();
            String sql = "INSERT INTO user (username, password, address, phone) VALUES (?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            pstmt.setString(3, address);
            pstmt.setString(4, phone);

            int rows = pstmt.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            Util.close(conn, pstmt, null);
        }
    }

    // 查询用户（根据用户名或ID）
    public static User getUser(String username, Integer userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = Util.getConn();
            String sql = "SELECT * FROM user WHERE username = ? OR user_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, username);
            pstmt.setInt(2, userId != null ? userId : -1);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setAddress(rs.getString("address"));
                user.setPhone(rs.getString("phone"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Util.close(conn, pstmt, rs);
        }
        return null;
    }

    // 添加商品
    public static boolean addProduct(String name, double price, int stock) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = Util.getConn();
            String sql = "INSERT INTO product (name, price, stock) VALUES (?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setDouble(2, price);
            pstmt.setInt(3, stock);

            int rows = pstmt.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            Util.close(conn, pstmt, null);
        }
    }

    // 查询商品（根据名称或ID）
    public static Product getProduct(String name, Integer productId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = Util.getConn();
            String sql = "SELECT * FROM product WHERE name = ? OR product_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setInt(2, productId != null ? productId : -1);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                Product product = new Product();
                product.setProductId(rs.getInt("product_id"));
                product.setName(rs.getString("name"));
                product.setPrice(rs.getDouble("price"));
                product.setStock(rs.getInt("stock"));
                return product;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Util.close(conn, pstmt, rs);
        }
        return null;
    }

    // 查询订单详情（关联用户和商品表）
    public static List<OrderDetail> getOrderDetails() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<OrderDetail> orderList = new ArrayList<>();

        try {
            conn = Util.getConn();
            stmt = conn.createStatement();
            String sql = "SELECT o.order_id, u.username, p.name, o.order_time, o.total_amount " +
                    "FROM `order` o " +
                    "JOIN user u ON o.user_id = u.user_id " +
                    "JOIN product p ON o.product_id = p.product_id";

            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                OrderDetail order = new OrderDetail();
                order.setOrderId(rs.getInt("order_id"));
                order.setUsername(rs.getString("username"));
                order.setProductName(rs.getString("name"));
                order.setOrderTime(rs.getTimestamp("order_time").toLocalDateTime());
                order.setTotalAmount(rs.getDouble("total_amount"));
                orderList.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Util.close(conn, stmt, rs);
        }
        return orderList;
    }
    // 获取所有商品
// 获取所有商品
    public static List<Product> getAllProducts() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Product> productList = new ArrayList<>();

        try {
            conn = Util.getConn();
            stmt = conn.createStatement();
            String sql = "SELECT * FROM product";
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                Product product = new Product();
                product.setProductId(rs.getInt("product_id"));
                product.setName(rs.getString("name"));
                product.setPrice(rs.getDouble("price"));
                product.setStock(rs.getInt("stock"));
                productList.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Util.close(conn, stmt, rs);
        }
        return productList;
    }

    // 获取用户订单（根据用户ID过滤）
// 根据用户ID获取订单（带商品和用户信息）
    public static List<OrderDetail> getOrdersByUser(int userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<OrderDetail> orderList = new ArrayList<>();

        try {
            conn = Util.getConn();
            String sql = "SELECT o.order_id, u.username, p.name, o.order_time, o.total_amount " +
                    "FROM `order` o " +
                    "JOIN user u ON o.user_id = u.user_id " +
                    "JOIN product p ON o.product_id = p.product_id " +
                    "WHERE o.user_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                OrderDetail order = new OrderDetail();
                order.setOrderId(rs.getInt("order_id"));
                order.setUsername(rs.getString("username"));
                order.setProductName(rs.getString("name"));
                order.setOrderTime(rs.getTimestamp("order_time").toLocalDateTime());
                order.setTotalAmount(rs.getDouble("total_amount"));
                orderList.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Util.close(conn, pstmt, rs);
        }
        return orderList;
    }
}